<?php

namespace App\Http\Controllers\Attendance;

use App\Http\Controllers\Controller;
use App\Imports\Attendance\AttendanceImport;
use App\Models\Attendance\Attendance;
use App\Models\Attendance\AttendanceData;
// use App\Models\MaintainRepair\MrApply;
use App\Models\Personnel\PersonnelCleaner;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class AttendanceBusController extends Controller
{
    public function attendanceList(Request $request)
    {
        $params = $request->all();
        $actualSignTime = Attendance::query()->groupBy('actual_sign_time')
            ->orderByDesc('actual_sign_time')
            ->first()
            ->actual_sign_time;

        if (isset($params['date'])) {
            $params['date'] = date('Y-m-d', strtotime($params['date']));
        } else {
            $params['date'] = date('Y-m-d', strtotime($actualSignTime));
        }
        $data = $this->getAttendanceList($params, $actualSignTime)
            ->orderBy('license_plate_num', 'asc')
            ->get()
            ->toArray();
        if ($data) {
            $data = $this->transData2($data, $params);
        }

        return response()->formatJson([
            'attendanceBusRecord' => $data, 'actualSignTime' => $params['date'],
        ]);
    }

    public function getAttendanceList($params, $actualSignTime): Builder
    {
        $cleaner = PersonnelCleaner::query()->get()->pluck('name')->toArray();
        $query = Attendance::query()->whereNotIn('driver', $cleaner);

        if (isset($params['license_plate_num'])) {
            $query->where('license_plate_num', 'like', '%' . $params['license_plate_num'] . '%');
        }

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('line', $params['line']);
        }

        if (isset($params['date'])) {
            $date = date('Y-m-d', strtotime($params['date']));
        } else {
            $date = date('Y-m-d', strtotime($actualSignTime));
        }
        $query->whereDate('actual_sign_time', $date);

        return $query;
    }

    public function transData2($data, $params)
    {
        $data = collect($data)->groupBy('license_plate_num')->toArray();
        $postData = [];
        foreach ($data as $licensePlateNum => $item) {
            $drivers = $this->transData($item, $params);
            foreach ($drivers as $k => $driver) {
                $postData[] = $driver;
            }
        }

        return $postData;
    }

    public function transData($data, $params)
    {
        $festival = [
            '2020-01-01',
            '2020-01-24',
            '2020-01-25',
            '2020-01-26',
            '2020-04-04',
            '2020-05-01',
            '2020-06-25',
            '2020-10-01',
            '2020-10-02',
            '2020-10-03',
            '2020-10-04',
        ];
        $data = collect($data)->groupBy('driver')->toArray();
        $postData = [];
        $otherFields = [
            '正常班'  => 'normalTime',
            '加班'   => 'overtime',
            '半天修车' => 'halfFixTime',
            '全天修车' => 'fullFixTime',
            '请假'   => 'leaveTime',
            '停运'   => 'stopTime',
            '晚上加班' => 'nightOvertime',
        ];
        $attendanceData = AttendanceData::query()->where('date', $params['date'])->get();
        // $mrData = MrApply::query()->where('date', $params['date'])->get();
        foreach ($data as $name => $driver) {
            $otherData = $attendanceData->where('name', $name)->all();
            $other = [
                'normalTime'    => '0',
                'overtime'      => '0',
                'halfFixTime'   => '0',
                'fullFixTime'   => '0',
                'leaveTime'     => '0',
                'stopTime'      => '0',
                'nightOvertime' => '0',
            ];
            if ($otherData) {
                $types = collect($otherData)->groupBy('type')->toArray();
                foreach ($types as $type => $v) {
                    foreach ($otherFields as $filed => $key) {
                        if ($filed === $type) {
                            // $other[$key] = count($v);
                            if (count($v) == count($v, COUNT_RECURSIVE)) {
                                //COUNT_RECURSIVE，也可以换成1
                                //  echo '是一维数组';
                                $other[$key] = $v['duration'];
                            } else {
                                //  echo '不是一维数组';
                                $other[$key] = $v[0]['duration'];
                            }
                        }
                    }
                }
            }
            $count = count($driver);
            $minDate = searchMin($driver, 'actual_sign_time');
            $minHour = UTC2Normal($minDate, 'H:i:s');
            $maxDate = searchMax($driver, 'actual_sign_time');
            $maxHour = UTC2Normal($maxDate, 'H:i:s');
            $workHour = number_format((strtotime($maxDate) - strtotime($minDate)) % 86400 / 3600, 1);
            $maxHours = UTC2Normal($maxDate, 'H');
            $normalHour = $maxHours <= 12 ? 4.5 : 5.5; //正常
            if ($workHour >= $normalHour && $workHour < 10) {
                $normalTime = 0.5;
            } elseif ($workHour >= 10) {
                $normalTime = 1;
            } else {
                $normalTime = 0;
            }
            $line = implode(',', array_unique(array_column($driver, 'line')));
            if ($other['normalTime'] != 0) {
                $status = '正常';
                $normal = $other['normalTime'];
            } else {
                $status = $workHour >= $normalHour ? '正常' : '异常';
                $normal = $status === '正常' ? $normalTime : 0;
            }
            // 正常班
            $other['normalTime'] =  $normal;
            // 加班
            $other['overtime'] = in_array($params['date'], $festival, true) ? 1 : 0;
            // 晚上加班
            $other['nightOvertime'] = $maxHour >= '21:00:00' ? 1 : 0;
            // 修车
            $fullFixTime =  $other['fullFixTime'] ? 1 : 0;
            $halfFixTime =  $other['halfFixTime']  ? 0.5 : 0;
            $other['fixTime'] = $fullFixTime ? $fullFixTime : $halfFixTime;
            $attendance = [
                'driver'            => $name,
                'count'             => $count,
                'signRange'         => $minHour . ' - ' . $maxHour,
                'line'              => implode(',', array_unique(array_column($driver, 'line'))),
                'license_plate_num' => implode(',', array_unique(array_column($driver, 'license_plate_num'))),
                'status'            => $status,
                'workHour'          => $workHour,
            ];
            $postData[] = array_merge($attendance, $other);
        }

        return $postData;
    }

    /**
     * 创建车辆信息
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function storeAttendance(Request $request)
    {
        $params = $request->all();
        if ($params['license_plate_num']) {
            $params['license_plate_num'] = Str::of($params['license_plate_num'])->replace('陕G', '');
        }

        $charteredCar = new Attendance($params);
        $result = $charteredCar->save();
        $message = $result ? '添加成功' : '添加失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 修改文章
     *
     * @param  Request  $request
     * @param           $id
     *
     * @return mixed
     */
    public function updateAttendance(Request $request, $id)
    {
        $params = $request->all();
        if ($params['license_plate_num']) {
            $params['license_plate_num'] = Str::of($params['license_plate_num'])->replace('陕G', '');
        }

        $charteredCar = Attendance::query()->find($id);

        $result = $charteredCar->update($params);

        $message = $result ? '修改成功' : '修改失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 删除车辆信息
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function destroyAttendance(Request $request)
    {
        $result = false;
        if ($request->has('ids')) {
            $ids = explode(',', $request['ids']);
            $charteredCarCount = Attendance::destroy($ids);
            $result = $charteredCarCount !== 0;
        }

        $message = $result ? '删除成功' : '删除失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 获取上个月的天数
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function attendanceAccountingDate(Request $request)
    {
        $params = $request->all();
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
            if ($params['date'] === '1970-01-01') {
                $params['date'] = date('Y-m-01');
            }
        } else {
            $params['date'] = date('Y-m-01');
        }
        //每条线路   车辆数
        $line = DB::table('akbus_bus_distribution')->groupBy('line')->get()->toArray();
        $result = $this->dateData($params['date']);
        return response()->formatJson(['result' => $result, 'line' => $line]);
    }

    public function dateData($params)
    {
        $curr_date = date(date('Y-m', strtotime($params)), strtotime('-1 month'));
        $up_date = date('Y-m', strtotime("$curr_date -1 month"));
        $days = date('t', strtotime($up_date));
        $result = [];
        $date_days = 26;
        for ($i = 0; $i < $days; $i++) {
            if ($days === 31) {
                if ($date_days + $i > 31) {
                    $d = (($date_days + $i) - 31) < 10 ? '0' . (($date_days + $i) - 31) : ($date_days + $i) - 31;
                    $date = date('Y-m-' . $d, strtotime($curr_date));
                } else {
                    $d = ($date_days + $i) < 10 ? '0' . ($date_days + $i) : ($date_days + $i);
                    $date = date('Y-m-' . $d, strtotime($up_date));
                }
            } else {
                if ($days === 30) {
                    if ($date_days + $i > 30) {
                        $d = (($date_days + $i) - 30) < 10 ? '0' . (($date_days + $i) - 30) : ($date_days + $i) - 30;
                        $date = date('Y-m-' . $d, strtotime($curr_date));
                    } else {
                        $d = ($date_days + $i) < 10 ? '0' . ($date_days + $i) : ($date_days + $i);
                        $date = date('Y-m-' . $d, strtotime($up_date));
                    }
                } else {
                    if ($days === 28) {
                        if ($date_days + $i > 28) {
                            $d = (($date_days + $i) - 28) < 10 ? '0' . (($date_days + $i) - 28) : ($date_days + $i) - 28;
                            $date = date('Y-m-' . $d, strtotime($curr_date));
                        } else {
                            $d = ($date_days + $i) < 10 ? '0' . ($date_days + $i) : ($date_days + $i);
                            $date = date('Y-m-' . $d, strtotime($up_date));
                        }
                    } else {
                        if ($days === 29) {
                            if ($date_days + $i > 29) {
                                $d = (($date_days + $i) - 29) < 10 ? '0' . (($date_days + $i) - 29) : ($date_days + $i) - 29;
                                $date = date('Y-m-' . $d, strtotime($curr_date));
                            } else {
                                $d = ($date_days + $i) < 10 ? '0' . ($date_days + $i) : ($date_days + $i);
                                $date = date('Y-m-' . $d, strtotime($up_date));
                            }
                        }
                    }
                }
            }
            $result[$i]['d'] = $d;
            $result[$i]['date'] = $date;
        }
        return $result;
    }

    /**
     * 考勤统计列表
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function attendanceAccounting(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
            if ($params['date'] === '1970-01-01') {
                $params['date'] = date('Y-m-01');
            }
        } else {
            $params['date'] = date('Y-m-01');
        }
        $result = $this->attendanceTransData($params)->groupBy('license_plate_num')->get()->toArray();

        foreach ($result as $key => $v) {
            $monthDaily = json_decode($result[$key]['monthDaily'], true);
            foreach ($monthDaily as $kv) {
                $result[$key][$kv['date_t']] = $kv['date_m'];
            }
        }
        $dataCount = $this->attendanceTransData($countParams)->groupBy('license_plate_num')->count();
        return response()->formatJson(['result' => $result, 'count' => $dataCount, 'date' => $params['date']]);
    }

    public function attendanceTransData($params)
    {
        $query = DB::table('akbus_attendance_accounting');
        if (isset($params['date'])) {
            $time = strtotime($params['date']);
            if ($params['date'] === '1970-01-01') {
                $time = strtotime(date('Y-m-01'));
            }
        } else {
            $time = strtotime(date('Y-m-01'));
        }

        $date = date('Y-m-01', $time);
        $query->where('date', '=', $date);

        if (isset($params['license_plate_num'])) {
            $query->where('license_plate_num', 'like', '%' . $params['license_plate_num'] . '%');
        }
        if (isset($params['line']) && $params['line'] !== -1) {
            $query->where('line', '=', $params['line']);
        }
        if (isset($params['pageNumber'], $params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }
        return $query->orderByDesc('id');
    }

    /**
     * 生成考勤数据
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function attendanceAccountingData(Request $request)
    {
        $params = $request->all();
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
            if ($params['date'] === '1970-01-01') {
                $params['date'] = date('Y-m-01');
            }
        } else {
            $params['date'] = date('Y-m-01');
        }
        $data_days = $this->dateData($params['date']);
        $result = $this->attendanceDataTransData($params)->groupBy('license_plate_num')->get()->toArray();
        $datainfo = [];
        foreach ($result as $key => $v) {
            $datainfo[$key]['license_plate_num'] = $v['license_plate_num'];
            $datainfo[$key]['line'] = $v['line'];
            $datainfo[$key]['driver'] = $v['driver'];
            $datainfo[$key]['date'] = $params['date'];
            $total_d = 0;
            $date = [];
            foreach ($data_days as $kd => $vd) {
                $date[$kd]['date_t'] = $vd['d'];
                $date[$kd]['date_m'] = DB::table('akbus_attendance')
                    ->where('actual_sign_time', 'like', '%' . $vd['date'] . '%')
                    ->where('driver', $v['driver'])
                    ->count() / 2;
                $total_d = (float) $total_d + (float) $date[$kd]['date_m'];
            }
            $datainfo[$key]['total_d'] = $total_d;
            $datainfo[$key]['monthDaily'] = json_encode($date);

            $attendance = DB::table('akbus_attendance_accounting')->where('date', $date)->where(
                'license_plate_num',
                $v['license_plate_num']
            )->first();
            if (isset($attendance['id'])) {
                unset($datainfo[$key]['date'], $datainfo[$key]['license_plate_num']);
                DB::table('akbus_attendance_accounting')->where('id', $attendance['id'])->update($datainfo[$key]);
            } else {
                DB::table('akbus_attendance_accounting')->insertGetId($datainfo[$key]);
            }
        }
        return response()->formatJson(['result' => true]);
    }

    public function attendanceDataTransData($params)
    {
        $query = DB::table('akbus_attendance');
        if (isset($params['date'])) {
            $time = strtotime($params['date']);
            if ($params['date'] === '1970-01-01') {
                $time = strtotime(date('Y-m-01'));
            }
        } else {
            $time = strtotime(date('Y-m-01'));
        }

        $start_date = date('Y-m-26', strtotime(date('Y', $time) . '-' . (date('m', $time) - 1) . '-26'));
        $end_date = date('Y-m-25', strtotime(date('Y', $time) . '-' . (date('m', $time)) . '-25'));
        $query->where('actual_sign_time', '>=', $start_date);
        $query->where('actual_sign_time', '<=', $end_date);

        return $query->orderByDesc('id');
    }

    /**
     * 详情
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function attendanceAccountingInfo(Request $request)
    {
        $params = $request->all();
        if (isset($params['id'])) {
            $result = DB::table('akbus_attendance_accounting')->where('id', $params['id'])->first();
        } else {
            $result = [];
        }
        return response()->formatJson(['result' => $result]);
    }

    /**
     * 编辑
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function editAttendanceAccounting(Request $request)
    {
        $params = $request->all();
        $id = $params['id'];
        unset($params['id'], $params['date']);
        $params['monthDaily'] = json_encode($params['monthDaily']);
        $result = DB::table('akbus_attendance_accounting')->where('id', $id)->update($params);

        $message = $result ? '编辑成功' : '编辑失败';

        return response()->formatJson(['result' => $result], $message);
    }

    /**
     * 导出考勤
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportAttendanceAccounting(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
            'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN',
        ];
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
            if ($params['date'] === '1970-01-01') {
                $params['date'] = date('Y-m-01');
            }
        } else {
            $params['date'] = date('Y-m-01');
        }
        $data_days = $this->dateData($params['date']);
        $data = $this->attendanceTransData($params)->groupBy('license_plate_num')->get()->toArray();
        foreach ($data as $key => $v) {
            $total_d = 0;
            foreach ($data_days as $kd => $vd) {
                $data[$key][$vd['d']] = DB::table('akbus_attendance')
                    ->where('actual_sign_time', 'like', $vd['date'] . '%')
                    ->where('driver', $v['driver'])
                    ->count() / 2;
                $total_d = (float) $total_d + (float) $data[$key][$vd['d']];
            }
            $data[$key]['total_d'] = $total_d;
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '考  勤  表 ')
            ->setCellValue('A2', $params['date'])
            ->setCellValue('A3', '车牌号')->setCellValue('B3', '姓名')->setCellValue('C3', '线路');
        $l = 3;
        foreach ($data_days as $kd => $vd) {
            $spreadsheet->setActiveSheetIndex(0)->setCellValue($Letter[$l + $kd] . '3', $vd['d']);
        }

        // 合并行、列
        if (count($data_days) === 30) {
            $spreadsheet->setActiveSheetIndex(0)->setCellValue('AH3', '合计');
            $spreadsheet->setActiveSheetIndex(0)->setCellValue('AH4', '小计');
            $spreadsheet->setActiveSheetIndex(0)->setCellValue('AI4', '加班');
            $spreadsheet->setActiveSheetIndex(0)->setCellValue('AJ4', '修车');
            $spreadsheet->setActiveSheetIndex(0)->setCellValue('AK4', '停运');
            $spreadsheet->setActiveSheetIndex(0)->setCellValue('AL4', '晚上加班');
            $spreadsheet->setActiveSheetIndex(0)->setCellValue('AM3', '备注');
        } else {
            if (count($data_days) === 29) {
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('AG3', '合计');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('AG4', '小计');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('AH4', '加班');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('AI4', '修车');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('AJ4', '停运');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('AK4', '晚上加班');
                $spreadsheet->setActiveSheetIndex(0)->setCellValue('AL3', '备注');
            } else {
                if (count($data_days) === 28) {
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AF3', '合计');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AF4', '小计');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AG4', '加班');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AH4', '修车');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AI4', '停运');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AJ4', '晚上加班');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AK3', '备注');
                } else {
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AI3', '合计');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AI4', '小计');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AJ4', '加班');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AK4', '修车');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AL4', '停运');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AM4', '晚上加班');
                    $spreadsheet->setActiveSheetIndex(0)->setCellValue('AN3', '备注');
                }
            }
        }

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $num = 4;
        $c = count($data);
        for ($i = 0; $i < $c; $i++) {
            $spreadsheet->getActiveSheet()->setCellValue('A' . $num, $data[$i]['license_plate_num']);
            $spreadsheet->getActiveSheet()->setCellValue('B' . $num, $data[$i]['driver']);
            $spreadsheet->getActiveSheet()->setCellValue('C' . $num, $data[$i]['line']);

            $date_d = 26;
            $date_i = 0;
            for ($d = 3; $d < count($data_days) + 3; $d++) {
                if (count($data_days) === 31) {
                    if ($date_d + $date_i > 31) {
                        $dd = (($date_d + $date_i) - 31) < 10 ? '0' . (($date_d + $date_i) - 31) : ($date_d + $date_i) - 31;
                    } else {
                        $dd = ($date_d + $date_i) < 10 ? '0' . ($date_d + $date_i) : ($date_d + $date_i);
                    }
                } else {
                    if ($date_d + $date_i > 30) {
                        $dd = (($date_d + $date_i) - 30) < 10 ? '0' . (($date_d + $date_i) - 30) : ($date_d + $date_i) - 30;
                    } else {
                        $dd = ($date_d + $date_i) < 10 ? '0' . ($date_d + $date_i) : ($date_d + $date_i);
                    }
                }
                $spreadsheet->getActiveSheet()->setCellValue($Letter[$d] . $num, $data[$i][$dd]);
                $date_i++;
            }

            if (count($data_days) === 30) {
                $spreadsheet->getActiveSheet()->setCellValue($Letter[33] . $num, $data[$i]['total_d']);
            } else {
                $spreadsheet->getActiveSheet()->setCellValue($Letter[34] . $num, $data[$i]['total_d']);
            }
            $num++;
        }

        $spreadsheet->setActiveSheetIndex(0)->setCellValue('AI4', '小计');
        // 合并行、列
        if (count($data_days) === 30) {
            $spreadsheet->getActiveSheet()->mergeCells('A1:AM1');
            $spreadsheet->getActiveSheet()->mergeCells('A2:AM2');
            $spreadsheet->getActiveSheet()->mergeCells('AH3:AL3');
            for ($l3 = 0; $l3 < 33; $l3++) {
                $spreadsheet->getActiveSheet()->mergeCells($Letter[$l3] . '3:' . $Letter[$l3] . '5');
            }
            for ($l4 = 33; $l4 < 37; $l4++) {
                $spreadsheet->getActiveSheet()->mergeCells($Letter[$l4] . '4:' . $Letter[$l4] . '5');
            }
            $spreadsheet->getActiveSheet()->mergeCells('AM4:AM5');
        } else {
            if (count($data_days) === 29) {
                $spreadsheet->getActiveSheet()->mergeCells('A1:AL1');
                $spreadsheet->getActiveSheet()->mergeCells('A2:AL2');
                $spreadsheet->getActiveSheet()->mergeCells('AG3:AK3');
                for ($l3 = 0; $l3 < 33; $l3++) {
                    $spreadsheet->getActiveSheet()->mergeCells($Letter[$l3] . '3:' . $Letter[$l3] . '5');
                }
                for ($l4 = 33; $l4 < 37; $l4++) {
                    $spreadsheet->getActiveSheet()->mergeCells($Letter[$l4] . '4:' . $Letter[$l4] . '5');
                }
                $spreadsheet->getActiveSheet()->mergeCells('AL4:AL5');
            } else {
                if (count($data_days) === 28) {
                    $spreadsheet->getActiveSheet()->mergeCells('A1:AK1');
                    $spreadsheet->getActiveSheet()->mergeCells('A2:AK2');
                    $spreadsheet->getActiveSheet()->mergeCells('AG3:AJ3');
                    for ($l3 = 0; $l3 < 33; $l3++) {
                        $spreadsheet->getActiveSheet()->mergeCells($Letter[$l3] . '3:' . $Letter[$l3] . '5');
                    }
                    for ($l4 = 33; $l4 < 37; $l4++) {
                        $spreadsheet->getActiveSheet()->mergeCells($Letter[$l4] . '4:' . $Letter[$l4] . '5');
                    }
                    $spreadsheet->getActiveSheet()->mergeCells('AK4:AK5');
                } else {
                    $spreadsheet->getActiveSheet()->mergeCells('A1:AN1');
                    $spreadsheet->getActiveSheet()->mergeCells('A2:AN2');
                    $spreadsheet->getActiveSheet()->mergeCells('AI3:AM3');
                    for ($l3 = 0; $l3 < 34; $l3++) {
                        $spreadsheet->getActiveSheet()->mergeCells($Letter[$l3] . '3:' . $Letter[$l3] . '5');
                    }
                    for ($l4 = 34; $l4 < 39; $l4++) {
                        $spreadsheet->getActiveSheet()->mergeCells($Letter[$l4] . '4:' . $Letter[$l4] . '5');
                    }
                    $spreadsheet->getActiveSheet()->mergeCells('AN3:AN5');
                }
            }
        }

        //  设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(8.38);
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(38);
        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(38);
        $spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('A2')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);

        for ($l5 = 0; $l5 < (count($data_days) + 9); $l5++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$l5] . '3')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$l5] . '4')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$l5] . '5')->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }

        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        if (count($data_days) === 30) {
            for ($i = 0; $i < 40; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i] . '3')->getFont()->setBold(true);
            }
        }
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('考勤');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        $spreadsheet->setActiveSheetIndex(0);
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="考勤.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     *  导入行政人员数据
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function importAttendance(Request $request)
    {
        $urlParse = parse_url($request->input('url'));
        $excelData = Excel::toArray(new AttendanceImport, public_path() . $urlParse['path']);
        $fullData = $excelData[0];

        // 删除第一行
        array_shift($fullData);
        array_shift($fullData);

        $fields = [
            'index',
            'department',
            'line',
            'driver',
            'driver_card_num',
            'license_plate_num',
            'self_numbering',
            'should_sign_time',
            'actual_sign_time',
            'zhunwu',
        ];

        $data = [];
        foreach ($fullData as $row) {
            $item = [];
            foreach ($fields as $index => $field) {
                $item[$field] = $row[$index];
            }

            unset($item['department'], $item['index']);
            if ($item['driver']) {
                // 删除姓名空格
                $item['driver'] = trim(str_replace(' ', '', $item['driver']));
            }
            if ($item['license_plate_num']) {
                // 删除陕G
                $item['license_plate_num'] = Str::of($item['license_plate_num'])->replace('陕G', '');
            }
            if ($item['driver_card_num']) {
                $item['driver_card_num'] = (int) $item['driver_card_num'];
            }
            $item['should_sign_time'] = $item['should_sign_time'] === '' ? null : $item['should_sign_time'];
            $item['created_at'] = date('Y-m-d H:i:s');

            $data[] = $item;
        }

        return response()->formatJson(Attendance::query()->insert($data), '导入成功');
    }
}
